package com.lmaos.basetools.dbconn;

import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;

import com.lmaos.basetools.base.SArrayUtils;
import com.lmaos.basetools.dbconn.DBConf.Sql;
import com.lmaos.basetools.sText.TextUtils;

public class SqlUtils {
	/**
	 * 说明：生成插入sql		<br>
	 * 时间：2017年6月19日 上午11:00:45
	 *
	 * @param tableName
	 * @param nameMapping 对象名与数据库名映射，key对象名，val数据库名称(请携带标准符号)
	 * @return
	 */
	public static Sql toInsertSql(String tableName,Map<String, String> nameMapping){
		StringBuilder insert = new StringBuilder("insert into ");
		String[] paramNames = new String[nameMapping.size()];
		insert.append(tableName).append(" (");
		int index = 0;
		for(Entry<String, String> entry : nameMapping.entrySet()){
			paramNames[index++] = entry.getKey();
			insert.append(entry.getValue()).append(",");
		} 
		if(index>0){
			insert.replace(insert.length()-1, insert.length(), ")");
		}else{
			insert.append(")");
		}
		insert.append(" values (");
		for (int i = 0; i < paramNames.length; i++) {
			insert.append("?,");
		}
		if(index>0){
			insert.replace(insert.length()-1, insert.length(), ")");
		}else{
			insert.append(")");
		}
		return new Sql(insert.toString(), paramNames);
	}
	/**
	 * 说明：生成更新sql		<br>
	 * 时间：2017年6月19日 上午11:00:24
	 *
	 * @param tableName
	 * @param nameMapping
	 * @param where
	 * @return
	 */
	public static Sql toUpdateSql(String tableName,Map<String, String> nameMapping,String where,String dbType){
		StringBuilder update = new StringBuilder(100);
		update.append("update ").append(toDBField(tableName, dbType)).append(" set ");
		for(Entry<String, String> entry : nameMapping.entrySet()){
			if(!TextUtils.isBlank(dbType)){
				update.append(toDBField(entry.getValue(),dbType)).append("=").append("#{").append(entry.getKey()).append("},");
			}else{
				update.append(entry.getValue()).append("=").append("#{").append(entry.getKey()).append("},");
			}
		} 
		if(nameMapping.size()>0){
			update.replace(update.length()-1, update.length(), " ");
		}else{
			update.append(" ");
		}
		if(!TextUtils.isBlank(where)){
			update.append("where ").append(toDBWhere(where, dbType));
		}
		return DBConf.toSql(update.toString());
	}
	/**
	 * 说明：获得一个查询sql		<br>
	 * 时间：2017年9月4日 下午7:50:36
	 *
	 * @param tableName 表名
	 * @param where		查询条件
	 * @param dbType	数据库类型
	 * @return
	 */
	public static Sql toSimpleSelectSql(String tableName,String where, String dbType){
		StringBuilder select = new StringBuilder(100);
		select.append("select * from ").append(toDBField(tableName, dbType));
		if(!TextUtils.isBlank(where)){
			select.append(" where ").append(toDBWhere(where, dbType));
		}
		return DBConf.toSql(select.toString());
	}
	public static String toDBField(String dbfieldName,String dbType){
		if("`[]\"".indexOf(dbfieldName.charAt(0))!=-1){
			return dbfieldName;
		}
		dbType = dbType.toLowerCase();
		if(dbType.equals("mysql")){
			return TextUtils.getString("", "`",dbfieldName.replace(".", "`.`"),"`");
		}else if(dbType.equals("sqlserver")){
			return TextUtils.getString("", "[",dbfieldName.replace(".", "].["),"]");
		}else if(dbType.equals("oracle")){
			return TextUtils.getString("", "\"",dbfieldName.replace(".", "\".\""),"\"");
		}else{
			return dbfieldName;
		}
	}
	public static String toDBWhere(String dbWhere,String dbType){
		StringBuilder where = new StringBuilder(100);
		String dbWhereFgs[] = dbWhere.split("\\ ");
		boolean zf = false;
		for (String dbWhereFg : dbWhereFgs) {
			String dbField_value[] = dbWhereFg.split("=");
			if(!zf&&isZF(dbWhereFg)){
				zf = true;
			}
			if(dbField_value.length==2){
				String dbField = toDBField(dbField_value[0].trim(), dbType);
				String dbValue = dbField_value[1].trim();
				where.append(dbField).append("=").append(dbValue).append(" ");
			}else if(dbWhereFg.startsWith("#")&&!zf){
				where.append(toDBField(dbWhereFg.substring(1),dbType)).append(" ");
			}else{
				where.append(dbWhereFg).append(" ");
			}
			if(zf&&isZF(dbWhereFg)){
				zf = false;
			}
		}
		return where.toString();
	}
	private static boolean isZF(String text){
		int index = text.indexOf('\'');
		if(index == -1){
			return false;
		}
		return (index==text.length()-1)
				||(index<text.length()&&text.charAt(index+1)!='\'');
	}
	
	public static String toStringParams(Object params){
		if(params==null){
			return null;
		}
		if(!SArrayUtils.isArrays(params)){
			
			return toStringParam(params);
		}
		int length = SArrayUtils.getLength(params);
		StringBuilder texts = new StringBuilder(100);
		for (int i = 0; i < length; i++) {
			String text = TextUtils.valueString(SArrayUtils.get(params, i));
			setStringParam(text, texts).append(",");
		}
		return texts.substring(0,texts.length()-1);
	}
	
	public static String toStringParam(Object param){
		if(param==null){
			return null;
		}
		StringBuilder texts = new StringBuilder();
		if(!SArrayUtils.isArrays(param)){
			return setStringParam(param, texts).toString();
		}else{
			return toStringParams(param);
		}
	}
	
	private static StringBuilder setStringParam(Object param,StringBuilder texts){
		if(param==null){
			return texts;
		}
		String left = "'";
		String right= "'";
		if(param instanceof String){
			String param_str = (String)param;
			if(param_str.charAt(0)=='\''){
				if(param_str.length() == 1||param_str.charAt(1)!='\''){
					left = "";
				}
			}
			if(param_str.charAt(param_str.length()-1)=='\''){
				if(param_str.length() == 1||param_str.charAt(param_str.length()-2)!='\''){
					right = "";
				}
			}
		}
		if(texts!=null){
			texts.append(left).append(param).append(right);
		}
		return texts;
	}
	public static void main(String[] args) {
		String tabeName = "`tb_role`";
		Map<String, String> nameMapping = new HashMap<String, String>();
		nameMapping.put("name_obj", "tc_name");
		System.out.println(toInsertSql(tabeName, nameMapping));
		
		System.out.println(toUpdateSql(tabeName, nameMapping, "username=#{username}","sqlserver"));
		
		System.out.println(toDBField("xxxxx.xx", "sqlserver"));
		
		System.out.println(toDBWhere("name=#{name} and xx=' #id in (6,7,8,9)' ' #id' #id", "sqlserver"));
		
		System.out.println(toSimpleSelectSql("uset", "name=#{name}", "mysql"));
		System.out.println(toStringParams("''''"));
		
	}
}
